#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
${HIVE_HOME} -S -e

create table  edu_dwd.dwd_fact_customer_relationship (

    id                          INT COMMENT '意向id',
    create_date_time            STRING COMMENT '创建时间',
    deleted                     TINYINT COMMENT '是否被删除（禁用）',
    customer_id                 INT COMMENT '所属客户id',
    origin_type                 STRING COMMENT '数据来源',
    itcast_school_id            INT COMMENT '校区Id',
    itcast_subject_id           INT COMMENT '学科Id',
    creator                     INT COMMENT '创建人',
    current_creator             INT COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                STRING COMMENT '创建者姓名',
    origin_channel              STRING COMMENT '来源渠道',
    payment_state               STRING COMMENT '支付状态',
    payment_time                STRING COMMENT '支付状态变动时间',
    course_id                   INT COMMENT '课程ID',
    course_name                 STRING COMMENT '课程名称',
    appeal_id                   INT COMMENT '申诉id',
    end_date                          STRING
) partitioned by (start_date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');
DROP TABLE edu_dwd.dwd_dim_customer_appeal;
create table edu_dwd.dwd_dim_customer_appeal(
     id                             INT COMMENT '主键',
    customer_relationship_first_id INT COMMENT '第一条客户关系id',
    employee_id                    INT COMMENT '申诉人',
    employee_name                  STRING COMMENT '申诉人姓名',
    employee_department_id         INT COMMENT '申诉人部门',
    employee_tdepart_id            INT COMMENT '申诉人所属部门',
    appeal_status                  INT COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
    dt                             STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');

DROP  TABLE  edu_dwd.dwd_dim_itcast_clazz;
create TABLE edu_dwd.dwd_dim_itcast_clazz(
    id                  INT COMMENT 'ems课程id(非自增)',
    create_date_time    STRING COMMENT '创建时间',
    update_date_time    STRING COMMENT '最后更新时间',
    deleted             TINYINT COMMENT '是否被删除（禁用）',
    itcast_school_id    INT COMMENT 'ems校区ID',
    itcast_school_name  STRING COMMENT 'ems校区名称',
    itcast_subject_id   INT COMMENT 'ems学科ID',
    itcast_subject_name STRING COMMENT 'ems学科名称',
    itcast_brand        STRING COMMENT 'ems品牌',
    clazz_type_state    STRING COMMENT '班级类型状态',
    clazz_type_name     STRING COMMENT '班级类型名称',
    teaching_mode       STRING COMMENT '授课模式',
    start_time          STRING COMMENT '开班时间',
    end_time            STRING COMMENT '毕业时间',
    comment             STRING COMMENT '备注',
    dt                  STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');


drop TABLE  edu_dwd.dwd_dim_employee;
create table edu_dwd.dwd_dim_employee(
  id                  INT COMMENT '员工id',
    real_name           STRING COMMENT '员工的真实姓名',
    phone               STRING COMMENT '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
    department_id       STRING COMMENT 'OA中的部门编号，有负值',
    department_name     STRING COMMENT 'OA中的部门名',
    job_number          STRING COMMENT '员工工号',
    last_login_date     STRING COMMENT '最后登录日期',
    creator             STRING COMMENT '创建人',
    create_date_time    STRING COMMENT '创建时间',
    update_date_time    STRING COMMENT '最后更新时间',
    deleted             TINYINT COMMENT '是否被删除（禁用）',
    scrm_department_id  INT COMMENT 'SCRM内部部门id',
    superior_leaders_id INT COMMENT '上级领导ID',
    tdepart_id          INT COMMENT '直属部门id',
    ems_user_name       STRING,
      dt                       STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');

drop TABLE edu_dwd.dwd_dim_scrm_department;
CREATE table edu_dwd.dwd_dim_scrm_department(
    id               INT COMMENT '部门id',
    name             STRING COMMENT '部门名称',
    parent_id        INT COMMENT '父部门id',
    create_date_time STRING COMMENT '创建时间',
    update_date_time STRING COMMENT '更新时间',
    tdepart_code     INT COMMENT '直属部门',
    creator          STRING COMMENT '创建者',
    depart_level     INT COMMENT '部门层级',
    depart_sign      INT COMMENT '部门标志，暂时默认1',
      dt                       STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');

drop TABLE edu_dwd.dwd_dim_customer_clue;

create table edu_dwd.dwd_dim_customer_clue(
    id                       INT COMMENT '线索id',
    create_date_time         STRING COMMENT '创建时间',
    update_date_time         STRING COMMENT '最后更新时间',
    customer_id              INT COMMENT '客户id',
    customer_relationship_id INT COMMENT '客户关系id',
    itcast_school_id         INT COMMENT '校区Id',
    itcast_school            STRING COMMENT '校区',
    itcast_subject_id        INT COMMENT '学科Id',
    itcast_subject           STRING COMMENT '学科',
    level                    STRING COMMENT '客户级别',
    origin_type              STRING COMMENT '数据来源渠道',
    valid                    TINYINT COMMENT '该线索是否是网资有效线索',
    clue_state               STRING COMMENT '线索状态',
     dt                       STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');